/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.*;



/**
 *
 * @author Trang
 */
public class CustomerDAO {
    private Connection con;
    public CustomerDAO()
    {
        con =  DBFactory.createConnection("onlineshopping");
    }
    public List<Customer> getAllCustomer()
    {
        List<Customer> result = new ArrayList<Customer>();
        try
        {
            String sql = "SELECT * FROM customer";
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
               
                Integer idCustomer = rs.getInt("idCustomer");
                String firstName = rs.getString("firstName"); 
                String lastname = rs.getString("lastname");
                String address = rs.getString("address");
                Date dob = rs.getDate("dob");
                String email = rs.getString("email");  
                //Date closeTime = rs.getDate("closeTime");
                String phone = rs.getString("phone");
                //Integer idMember = rs.getInt("MemberAccount_idMemberAccount");
                //Memberaccount memberaccount = this.getMemberAccountByID(idMember);
                
                Customer cus = new Customer(idCustomer, firstName, lastname, address,phone, dob, email);
                
                result.add(cus);
            }
        }
        catch(Exception e)
        {
            
        }
        return result;
    }
    public List<Customer> getAllCustomer(Integer sort)
    {
        List<Customer> result = new ArrayList<Customer>();
        String sortname ="";
        if(sort == 1) sortname = "firstName";
        else if(sort == 2) sortname ="lastname";
        else if (sort == 3) sortname="address";
        else  sortname = "dob";
        try
        {
            String sql = "SELECT * FROM customer ORDER BY "+sortname;
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
               
                Integer idCustomer = rs.getInt("idCustomer");
                String firstName = rs.getString("firstName"); 
                String lastname = rs.getString("lastname");
                String address = rs.getString("address");
                Date dob = rs.getDate("dob");
                String email = rs.getString("email");  
                //Date closeTime = rs.getDate("closeTime");
                String phone = rs.getString("phone");
                //Integer idMember = rs.getInt("MemberAccount_idMemberAccount");
                //Memberaccount memberaccount = this.getMemberAccountByID(idMember);
                
                Customer cus = new Customer(idCustomer, firstName, lastname, address,phone, dob, email);
                
                result.add(cus);
            }
        }
        catch(Exception e)
        {
            
        }
        return result;
    }
   public Memberaccount getMemberAccountByID(int aid)
   {
       Memberaccount acc = new Memberaccount();
       try
       {
            String sql = "SELECT * FROM memberaccount WHERE idMemberAccount= ?";
            PreparedStatement ps = con.prepareStatement(sql);
            
            ps.setInt(1, aid);
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
               
                Integer idMemberAccount = rs.getInt("idMemberAccount");
                String username = rs.getString("username"); 
                String password = rs.getString("password");
                Date openTime = rs.getDate("openTime");
                Date closeTime = rs.getDate("closeTime");
                String email = rs.getString("email");  
                String status = rs.getString("status");
                
                
                 acc = new Memberaccount(idMemberAccount, username, password, openTime, status);
            }
       }
       catch (SQLException ex) {
            Logger.getLogger(CustomerDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
       return acc;
   }
   public List<Customer> getAllCustomer( int offset, int length) {
        List<Customer> result = new ArrayList<Customer>();
        try {
            String sqlst = "SELECT * FROM customer"                
                    + " LIMIT ?, ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, offset);
            ps.setInt(2, length);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idCustomer = rs.getInt("idCustomer");
                String firstName = rs.getString("firstName"); 
                String lastname = rs.getString("lastname");
                String address = rs.getString("address");
                Date dob = rs.getDate("dob");
                String email = rs.getString("email");
                String phone = rs.getString("phone");
                //Date closeTime = rs.getDate("closeTime");
                //Integer idMember = rs.getInt("MemberAccount_idMemberAccount");
                //Memberaccount memberaccount = this.getMemberAccountByID(idMember);
                
               Customer cus = new Customer(idCustomer, firstName, lastname, address,phone, dob, email);
                
                result.add(cus);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    public boolean InsertCustomer(Customer customer) {
        boolean result = false;
        try {
            String sqlst1 = "INSERT INTO customer(firstName, lastName, address, phone, dob, email) "
                    + "VALUE(?, ?, ?, ?, ?, ?)";

            PreparedStatement ps = con.prepareStatement(sqlst1);
            ps.setString(1, customer.getFirstName());
            ps.setString(2, customer.getLastName());
            ps.setString(3, customer.getAddress());
            ps.setString(4, customer.getPhone());
            ps.setDate(5, new java.sql.Date (customer.getDob().getTime()));
            ps.setString(6, customer.getEmail());
//            ps.setDate(9, new java.sql.Date (staff.getDob().getTime()));
            
            ps.executeUpdate();
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(AuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    public Customer getCustomerFromCustomerID(Integer aid)
    {
        
        Customer result = new Customer();
        try
        {
            String sql = "SELECT * FROM customer WHERE idCustomer= ?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, aid);
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
               
                Integer idCustomer = rs.getInt("idCustomer");
                String firstName = rs.getString("firstName"); 
                String lastname = rs.getString("lastname");
                String address = rs.getString("address");
                Date dob = rs.getDate("dob");
                String email = rs.getString("email");  
                //Date closeTime = rs.getDate("closeTime");
                String phone = rs.getString("phone");
                //Integer idMember = rs.getInt("MemberAccount_idMemberAccount");
                //Memberaccount memberaccount = this.getMemberAccountByID(idMember);
                
                result = new Customer(idCustomer, firstName, lastname, address,phone, dob, email); 
            }
        }
        catch(Exception e)
        {
            
        }
        return result;
    }
}
